<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class User_vip_model extends CI_Model {
	function __construct(){
        parent::__construct();
    }
    
    
    /**
     * VIP申请
     */
    function apply($parms = array()){
    	//$parms = array('user_id','kefu_userid','kefu_addtime','vip_status','vip_remark','vip_money','vip_verify_remark','vip_verify_time');
    	$this->db->insert('zjy_user_vip', $parms);
		return $this->db->affected_rows();
    }
    
    function get_one($where = array(),$select='*'){
		$this->db->select($select);
		if($where){
			$this->db->where($where);
		}
		$this->db->limit(1);
		$query = $this->db->get('zjy_user_vip');
		return $query->row_array();
	}
	
	function update($data = array(),$where = array()){
		$this->db->where($where);
		$this->db->update('zjy_user_vip', $data);
		$res = $this->db->affected_rows();
		if($res > 0 && isset($where['user_id'])){
			$key = 'zjy_user_vip_'.$where['user_id'];
			$this->mycache->delete($key);
		}
		return $res;
    }
    
    function get_by_uid($user_id){
		$key = 'zjy_user_vip_' . $user_id;
		$this->mycache->delete($key);//@todo
		$row = $this->mycache->get_array($key);
		if($row == false){
			$this->db->where('user_id',$user_id);
			$query = $this->db->get('zjy_user_vip');
			$row = $query->row_array();
			if($row){
				$this->mycache->set_array($key,$row);
			}
		}
		return $row;
	}
	
	/**
	 * 是否有效VIP
	 */
	function is_vip($user_id){
		$row = self::get_by_uid($user_id);
		if (isset($row['user_id']) && $row['vip_status']==1 && time()<=$row['vip_verify_time']+3600*24*365){
			return true;
		}else{
			return false;
		}
	}
	
	function get_customers($data=array()){
		$_select = "p1.user_id,p1.username,p1.realname,p1.sex,p1.phone,p1.qq,p1.email,p1.add_time,p2.province,p2.city,p2.area,p4.collection";
		
		$_where = " where 1=1 ";
		if(isset($data['kefu_userid']) && $data['kefu_userid']>0){
			$_where .= " and p3.kefu_userid= '{$data['kefu_userid']}'";
		}
		if(isset($data['username']) && $data['username'] != ''){
			$_where .= " and p1.username like '%{$data['username']}%'";
		}
		if(isset($data['realname']) && $data['realname'] != ''){
			$_where .= " and p1.realname like '%{$data['realname']}%'";
		}
		if(isset($data['email']) && $data['email'] != ''){
			$_where .= " and p1.email like '%{$data['email']}%'";
		}
		
		$_order = " order by tender_money desc,p1.user_id desc ";
		if(isset($data['m_order']) && $data['m_order']=='collection'){
			$_order = " order by p4.collection desc,p1.add_time desc ";
		}else{
			$_order = " order by p1.add_time desc ";
		}
		
		$sql = "select $_select from zjy_user as p1 left join zjy_user_info as p2 on p1.user_id=p2.user_id 
		left join zjy_user_vip as p3 on p1.user_id=p3.user_id left join zjy_account as p4 on p1.user_id=p4.user_id " . $_where . $_order;
		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql);
			$list = $query->result_array();
			return $list;
		}else{
			$data['page'] = isset($data['page']) ? $data['page'] : 0;
			$data['epage'] = isset($data['epage']) ? $data['epage'] : 20;
			$_limit = " limit ".$data['page'].",".$data['epage'];
			$sql .= $_limit;
			$query = $this->db->query($sql);
			$list = $query->result_array();
		}
		//总记录条数
		$sql_count = "select count(*) as count from zjy_user as p1 left join zjy_user_info as p2 on p1.user_id=p2.user_id
			left join zjy_user_vip as p3 on p1.user_id=p3.user_id ".$_where;
		$query = $this->db->query($sql_count);
		$total_row = $query->row()->count;
		
		return array(
			'list' => $list,
			'count' => $total_row
		);
	}
	
	/**
	 * 客户当日投资明细
	 * @param $kefu_userid
	 * @param $page
	 */
	function get_customers_tender($kefu_userid=0, $page=0){
		$today = strtotime(date('Y-m-d'));
		$end = $today+3600*24;
		$epage = 20;
		$sql = "SELECT p1.account,p1.add_time,p3.username FROM zjy_borrow_tender AS p1 
			LEFT JOIN zjy_user_vip AS p2 ON p1.user_id=p2.user_id 
			LEFT JOIN zjy_user AS p3 ON p1.user_id=p3.user_id 
			WHERE p2.kefu_userid={$kefu_userid} AND p1.add_time>={$today} AND p1.add_time<{$end} 
			ORDER BY p1.add_time ASC LIMIT {$page},{$epage}";
		$query = $this->db->query($sql);
		$list = $query->result_array();
		
		$sql = "SELECT COUNT(id) AS count FROM zjy_borrow_tender AS p1 
			LEFT JOIN zjy_user_vip AS p2 ON p1.user_id=p2.user_id 
			WHERE p2.kefu_userid={$kefu_userid} AND p1.add_time>={$today} AND p1.add_time<{$end}";
		$query = $this->db->query($sql);
		$total_row = $query->row()->count;
		return array(
			'list' => $list,
			'count' => $total_row
		);
	}
	
	function get_invest_sum($data=array()){
		$sql = "SELECT SUM(t.account) AS money FROM zjy_borrow_tender t
LEFT JOIN zjy_borrow b ON t.borrow_id=b.id
LEFT JOIN zjy_user_vip v ON t.user_id=v.user_id 
LEFT JOIN zjy_user u ON t.user_id=u.user_id 
WHERE b.fstatus IN(1,3) AND b.ftype<5 AND v.kefu_userid='{$data['kefu_userid']}' AND u.is_self=0 AND t.add_time>'{$data['start']}' AND t.add_time<'{$data['end']}'";
		$query = $this->db->query($sql);
		$money1 = $query->row()->money;
		
		$sql = "select sum(p1.account) as money from zjy_yjb_tender as p1
		left join zjy_user_vip as p2 on p1.user_id=p2.user_id
		where p1.add_time>'{$data['start']}' and p1.add_time<'{$data['end']}' 
		and p1.time_limit_day>=30 and p2.kefu_userid='{$data['kefu_userid']}'";
		$query = $this->db->query($sql);
		$money2 = $query->row()->money;
		
		$money = intval($money1) + intval($money2);
		
		return $money;
		
	}
	
	/**
	 * //投标成功后的VIP检测  
	 * @param int $user_id
	 */
	function check_vip($user_id){
		$this->load->model('User_model');
		$this->load->model('Mydb_model', 'mydb');
		$user = $this->User_model->get_by_uid($user_id);
		$vipinfo = self::get_by_uid($user_id);
		$time = time();
		$ip = $this->input->ip_address();
		$days = 30;
		if ($vipinfo && $vipinfo['vip_status']==1 && $vipinfo['vip_money']>0){
			$days = cal_days_in_month(CAL_GREGORIAN, date('m',$vipinfo['vip_verify_time']), date('Y',$vipinfo['vip_verify_time']));
		}
		
		if ($vipinfo && $vipinfo['vip_status']==1 && $vipinfo['vip_money']>0 && $time-$vipinfo['vip_verify_time']<3600*24*$days){
			//是VIP且还未返还VIP费用且通过VIP时间不大于30天
			//判断投资是否达到10000
			$tmpStart = min($vipinfo['vip_verify_time'],strtotime(date('Y-m-d',$vipinfo['vip_verify_time'])));
			$sql = "SELECT SUM(p1.account) AS money FROM zjy_borrow_tender AS p1 LEFT JOIN zjy_borrow AS p2 ON p1.borrow_id=p2.id WHERE p1.user_id={$user_id} AND p1.add_time>{$tmpStart} AND p1.add_time<{$time} AND p2.ftype!=5 AND p2.is_day=0";
			$row = $this->mydb->query_sql($sql,'row');
			$money1 = $row['money'] ? $row['money'] : 0;
			$sql = "select sum(account) as money from zjy_yjb_tender where user_id={$user_id} and add_time>{$tmpStart} AND add_time<{$time} and time_limit_day>=30";
			$row2 = $this->mydb->query_sql($sql,'row');
			$money2 = $row2['money'] ? $row2['money'] : 0;
			$money = $money1 + $money2;
			if ($money>=10000){
				//返还VIP费用
				//事务开始
				$this->load->model('Account_model');
				$user_account = $this->Account_model->get_account($user['user_id']);
				$this->db->trans_begin();
				//返还会员费 VIP
				$log['user_id'] 		= $user_id;
				$log['ftype'] 			= VIP_REPAY;//要改成VIP费用返还
				$log['money'] 			= $vipinfo['vip_money'];
				$log['total'] 			= $user_account['total']+$log['money'];
				$log['use_money'] 		= $user_account['use_money']+$log['money'];
				$log['no_use_money'] 	= $user_account['no_use_money'];
				$log['collection'] 		= $user_account['collection'];
				$log['to_user'] 		= 1;
				$log['remark'] 			= "VIP费用返还";
				$log['add_time'] 		= $time;
				$log['addip'] 			= $ip;
				$mode = $user_id%100;
				$res = $this->db->insert('zjy_account_log_'.$mode, $log);
				if ($res){
					$res = $this->db->query("update zjy_account set total=total+{$log['money']},use_money=use_money+{$log['money']} where user_id={$user_id}");
				}
				if ($res){
					$res = self::update(array('vip_money'=>0), array('user_id'=>$user_id));
				}
				if ($res){
					//消息通知
					$this->load->model('Message_model', 'message');
					$msg = array(
						'sent_user' => 1,
						'receive_user' => $user_id,
						'name' => 'VIP费用返还',
						'fstatus' => 0,
						'message_type' => '1',
						'sented' => 0,
						'deltype' => 0,
						'content' => '您的VIP费用 '.$vipinfo['vip_money'].' 元已经返还到您的账户，请核实',
						'add_time' => $time,
						'addip' => $ip
					);
					$this->message->send($msg);
					$this->db->trans_commit();
				}else{
					$this->db->trans_rollback();
				}
			}
			
		}elseif ($vipinfo && $vipinfo['vip_status']==1 && $time-$vipinfo['vip_verify_time']>3600*24*365){
			//过期的VIP一个月内投资达到10000自动续期
			$stime = $time-3600*24*30;
			$sql = "SELECT SUM(p1.account) AS money FROM zjy_borrow_tender AS p1 LEFT JOIN zjy_borrow AS p2 ON p1.borrow_id=p2.id WHERE p1.user_id={$user_id} AND p1.add_time>{$stime} AND p1.add_time<{$time} AND p2.ftype!=5 AND p2.is_day=0";
			$row = $this->mydb->query_sql($sql,'row');
			$money1 = $row['money'] ? $row['money'] : 0;
			$sql = "select sum(account) as money from zjy_yjb_tender where user_id={$user_id} and add_time>{$stime} AND add_time<{$time} and time_limit_day>=30";
			$row2 = $this->mydb->query_sql($sql,'row');
			$money2 = $row2['money'] ? $row2['money'] : 0;
			$money = $money1 + $money2;
			if ($money>=10000){
				$vipSetArr = array(
					'vip_money' => 0,
					'vip_verify_time' => $vipinfo['vip_verify_time']+ 3600*24*365
				);
				self::update($vipSetArr,array('user_id'=>$user_id));
			}
		}elseif (!$vipinfo){
			//非VIP客户自注册起一个月内投资达到10000自动成为VIP；无需扣费； 随机客服
			if ($time - $user['add_time']<3600*24*30){
				$sql = "SELECT SUM(p1.account) AS money FROM zjy_borrow_tender AS p1 LEFT JOIN zjy_borrow AS p2 ON p1.borrow_id=p2.id WHERE p1.user_id={$user_id} AND p1.add_time>{$user['add_time']} AND p1.add_time<{$time} AND p2.ftype!=5 AND p2.is_day=0";
				$row = $this->mydb->query_sql($sql,'row');
				$money1 = $row['money'] ? $row['money'] : 0;
				$sql = "select sum(account) as money from zjy_yjb_tender where user_id={$user_id} and add_time>{$user['add_time']} and add_time<{$time} and time_limit_day>=30";
				$row2 = $this->mydb->query_sql($sql,'row');
				$money2 = $row2['money'] ? $row2['money'] : 0;
				$money = $money1 + $money2;
				if ($money>=10000){
					//$kefuList = $this->User_model->get_kefu_list();
					//$kefu_userid = array_rand($kefuList);//随机
					$kefu_userid = 0;//VIP通过但需要手动选择专属客服
					$vipSetArr = array(
						'user_id' => $user_id,
						'kefu_userid' => $kefu_userid,
						'kefu_addtime' => $time,
						'vip_status' => 1,//自动通过无需人工审核
						'vip_remark' => '',
						'vip_money' => 0,
						'vip_verify_remark' => 'VIP审核通过',
						'vip_verify_time' => $time,
					);
					self::apply($vipSetArr);
				}
			}
		}
	}
    
    
    
}